﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using ModelDinhMuc.Model;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using Microsoft.Office.Interop.Excel;

namespace TinhNguyenVatLieuSanBay
{
    public partial class FrmTinhNguyenVatLieuChuyenBay : Form
    {
        public static FrmTinhNguyenVatLieuChuyenBay frmtinhnguyenvatlieu = null;
        List<ChuyenBay> lstchuyenbay;
        List<ChiTietDungCuPhucVuChuyenBay> lstchitietdungcu;
        List<ChiTietDungCuPhucVuChuyenBay> lstchitietdungcuExcel;
        List<DungCuExcel> lstalldungcu=new List<DungCuExcel>();
        DINHMUCSANBAYDB db = new DINHMUCSANBAYDB();
        private static int countexcel=1;
        private static Microsoft.Office.Interop.Excel.ApplicationClass appExcel;
        private static Workbook newWorkbook = null;
        private static _Worksheet objsheet = null;
        string slkhachvip = null;
        string slkhachthuong = null;
        static HSSFWorkbook hssfworkbook;
        long slphicong = 0;
        long sltiepvien = 0;

        static void excel_init(String path)
        {
            string fullPath = Path.GetFullPath(path);
            appExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            if (System.IO.File.Exists(fullPath))
            {
                newWorkbook = appExcel.Workbooks.Open(fullPath, true, true);
                objsheet = (_Worksheet)appExcel.ActiveWorkbook.ActiveSheet;
            }
            else
            {
                MessageBox.Show("Unable to open file!");
                return;
            }
        }

        static string excel_getValue(string cellname)
        {
            string value = string.Empty;
            try
            {
                value = objsheet.get_Range(cellname).get_Value().ToString();
            }
            catch
            {
                value = "";
            }

            return value;
        }

        static void excel_close()
        {
            if (appExcel != null)
            {
                try
                {
                    newWorkbook.Close();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
                    appExcel = null;
                    objsheet = null;
                }
                catch (Exception ex)
                {
                    appExcel = null;
                    MessageBox.Show("Unable to release the Object " + ex.ToString());
                }
                finally
                {
                    GC.Collect();
                }
            }
        }

        private void readfileExcelimporttoList()
        {
            lstchuyenbay = new List<ChuyenBay>();
            excel_init(@"xls\ChuyenBay.xls");
            Microsoft.Office.Interop.Excel.Range usedRange = objsheet.UsedRange;
            int row = usedRange.Rows.Count;
            string cellngaybay = "C3";
            DateTime ngaybay = DateTime.Parse(excel_getValue(cellngaybay));
            for (int i = 6; i <= row; i++)
            {
                ChuyenBay chuyenbay = new ChuyenBay();
                string cellID= "A" + i;
                string cellMAMB = "B" + i;
                string cellMachuyenBay = "C" + i;
                string cellGioBay = "E" + i;
                string cellslkhach="F"+i;
                DateTime giobay = DateTime.FromOADate(double.Parse(excel_getValue(cellGioBay)));
                chuyenbay.ID = long.Parse(excel_getValue(cellID));
                chuyenbay.MaMB = excel_getValue(cellMAMB);
                chuyenbay.MaBAY = excel_getValue(cellMachuyenBay);
                chuyenbay.giobay = giobay.ToShortTimeString().ToString();
                chuyenbay.ngaybay = ngaybay.ToShortDateString().ToString();
                layslkhach(excel_getValue(cellslkhach));
                if (slkhachthuong == null)
                {
                    slkhachthuong = "0";
                }
                if (slkhachvip == null)
                {
                    slkhachvip = "0";
                }
                chuyenbay.slkhachvip = slkhachvip;
                chuyenbay.slkhachthuong = slkhachthuong;
                lstchuyenbay.Add(chuyenbay);
                slkhachvip = null;
                slkhachthuong = null;
            }
            chuyenBayBindingSource.DataSource = lstchuyenbay;
            excel_close();
        }

        private void layslkhach(string chuoicanxuly)
        {
            int tmp=0;
            char[] tmp1;
            char[] array = chuoicanxuly.ToArray();
            for (int i = 0; i < array.Count(); i++)
            {
                if (array[i].Equals('C'))
                {
                    slkhachvip = chuoicanxuly.Substring(0, i);
                    tmp = i;
                }
                if (array[i].Equals('Y'))
                {
                    tmp1 = chuoicanxuly.Substring(tmp, i-1).ToArray();
                    for (int j = 0; j < tmp1.Count(); j++)
                    {
                        if (char.IsDigit(tmp1[j]) == true)
                        {
                            slkhachthuong += tmp1[j].ToString();
                        }
                    }
                }
            }
        }

        private void InitchiTietdungcu()
        {
            try
            {
                lstchitietdungcu = new List<ChiTietDungCuPhucVuChuyenBay>();
                var item = gvchuyenbay.CurrentRow.DataBoundItem as ChuyenBay;
                long idcatalog = long.Parse(cmbcatalogngoaikhay.SelectedValue.ToString());
                if (item != null)
                {
                    if (item.MaMB != "B777")
                    {
                        lstchitietdungcu = db.SelectChiTietDungCuByMaMBAndMaChuyenBay(item.MaMB, item.MaBAY, idcatalog).ExecuteTypedList<ChiTietDungCuPhucVuChuyenBay>();
                       
                    }
                    else
                    {
                        List<ChiTietDungCuPhucVuChuyenBay> lsttmp = new List<ChiTietDungCuPhucVuChuyenBay>();
                        if (item.MaBAY == "CXRHAN")
                        {
                            #region Kiem tra dac biet ma chuyen bay B777
                            if (long.Parse(item.slkhachvip) < 27)
                            {
                                lsttmp = db.SelectChiTietDungCuByMaMBAndMaChuyenBay(item.MaMB, item.MaBAY, idcatalog).ExecuteTypedList<ChiTietDungCuPhucVuChuyenBay>();
                                if (idcatalog == 4)
                                {
                                    lstchitietdungcu = lsttmp.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(20)).ToList();
                                }
                                else
                                {
                                    lstchitietdungcu = lsttmp.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(6)).ToList();
                                }
                            }
                            else
                            {
                                lsttmp = db.SelectChiTietDungCuByMaMBAndMaChuyenBay(item.MaMB, item.MaBAY, idcatalog).ExecuteTypedList<ChiTietDungCuPhucVuChuyenBay>();
                                if (idcatalog == 4)
                                {
                                    lstchitietdungcu = lsttmp.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(21)).ToList();
                                }
                                else
                                {
                                    lstchitietdungcu = lsttmp.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(7)).ToList();
                                }
                            }
                            #endregion
                        }
                        else
                        {
                            #region Kiem tra dac biet ma chuyen bay B777
                            if (long.Parse(item.slkhachvip) < 27)
                            {
                                lsttmp = db.SelectChiTietDungCuByMaMBAndMaChuyenBay(item.MaMB, item.MaBAY, idcatalog).ExecuteTypedList<ChiTietDungCuPhucVuChuyenBay>();
                                if (idcatalog == 4)
                                {
                                    lstchitietdungcu = lsttmp.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(22)).ToList();
                                }
                                else
                                {
                                    lstchitietdungcu = lsttmp.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(8)).ToList();
                                }
                            }
                            else
                            {
                                lsttmp = db.SelectChiTietDungCuByMaMBAndMaChuyenBay(item.MaMB, item.MaBAY, idcatalog).ExecuteTypedList<ChiTietDungCuPhucVuChuyenBay>();
                                if (idcatalog == 4)
                                {
                                    lstchitietdungcu = lsttmp.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(23)).ToList();
                                }
                                else
                                {
                                    lstchitietdungcu = lsttmp.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(9)).ToList();
                                }
                            }
                            #endregion
                        }
                        
                    }
                }
                chiTietDungCuPhucVuChuyenBayBindingSource.DataSource = lstchitietdungcu;
            }
            catch
            {

            }
        }

        private void getchitietdungcuExcel(string mamaybay, string machuyenbay)
        {
            long idcatalog = long.Parse(cmbcatalogngoaikhay.SelectedValue.ToString());
            lstchitietdungcuExcel = db.SelectChiTietDungCuByMaMBAndMaChuyenBay(mamaybay, machuyenbay, idcatalog).ExecuteTypedList<ChiTietDungCuPhucVuChuyenBay>();
        }

        private void InitCatalog()
        {
            IList<Catalog> lstcatalog;
            lstcatalog = db.selectcatalogHaveParent(2).ExecuteTypedList<Catalog>();
            catalogBindingSource.DataSource = lstcatalog;
        }

        static void InitializeWorkbook()
        {
            FileStream file = new FileStream(@"xls\MauDungCuPhucVuChuyenBay.xls", FileMode.Open, FileAccess.Read);
            hssfworkbook = new HSSFWorkbook(file);
        }

        static void WriteToFile()
        {
            //Write the stream data of workbook to the root directory
            FileStream file = new FileStream(@"xls\CopyMauDungCuPhucVuChuyenBay" + countexcel + ".xls", FileMode.Create);
            
            hssfworkbook.Write(file);
            file.Close();
        }

        static void WriteToFileAnUong()
        {
            FileStream file = new FileStream(@"xls\CopyMauDungCuPhucVuChuyenBayAnVaUong" + countexcel + ".xls", FileMode.Create);

            hssfworkbook.Write(file);
            file.Close();
        }

        private void xuatexcelchitiet()
        {
            InitializeWorkbook();
            lstalldungcu = new List<DungCuExcel>();

            #region Sheet chi tiet dung cu cho tung chuyen bay
            ISheet s1 = hssfworkbook.GetSheet("Sheet1");
            int row = 0;
            long IDalldungcu = 0;
            string loaiphieu = cmbcatalogngoaikhay.Text;
            for (int i = 0; i < gvchuyenbay.Rows.Count; i++)
            {
                var item = gvchuyenbay.Rows[i].DataBoundItem as ChuyenBay;
                if (item.MaMB != "B777")
                {
                    getslphicongvatiepvien(item.MaMB, item.MaBAY);
                    getchitietdungcuExcel(item.MaMB, item.MaBAY);
                    IRow headerRow = s1.CreateRow(row);
                    headerRow.CreateCell(0).SetCellValue("Mã Máy Bay");
                    headerRow.CreateCell(1).SetCellValue(item.MaMB);
                    headerRow.CreateCell(2).SetCellValue("Giờ bay:");
                    headerRow.CreateCell(3).SetCellValue(item.giobay);
                    row = row + 1;
                    headerRow = s1.CreateRow(row);
                    headerRow.CreateCell(0).SetCellValue("Mã chuyến bay");
                    headerRow.CreateCell(1).SetCellValue(item.MaBAY);
                    headerRow.CreateCell(2).SetCellValue("Ngày bay");
                    headerRow.CreateCell(3).SetCellValue(item.ngaybay);
                    row = row + 1;
                    headerRow = s1.CreateRow(row);
                    headerRow.CreateCell(0).SetCellValue("Loại phiếu");
                    headerRow.CreateCell(1).SetCellValue(loaiphieu);
                    row = row + 1;
                    headerRow = s1.CreateRow(row);
                    headerRow.CreateCell(0).SetCellValue("Mã dụng cụ");
                    headerRow.CreateCell(1).SetCellValue("Tên dụng cụ");
                    headerRow.CreateCell(2).SetCellValue("Đơn vị");
                    headerRow.CreateCell(3).SetCellValue("SL khách VIP: " + item.slkhachvip.ToString());
                    headerRow.CreateCell(4).SetCellValue("SL khách Thường: " + item.slkhachthuong.ToString());
                    headerRow.CreateCell(5).SetCellValue("Số phi công: " + slphicong);
                    headerRow.CreateCell(6).SetCellValue("Số tiếp viên: " + sltiepvien);
                    foreach (var itemdung in lstchitietdungcuExcel)
                    {
                        row = row + 1;
                        IDalldungcu = IDalldungcu + 1;
                        headerRow = s1.CreateRow(row);
                        headerRow.CreateCell(0).SetCellValue(itemdung.MaDungCu);
                        headerRow.CreateCell(1).SetCellValue(itemdung.TenDungCu);
                        headerRow.CreateCell(2).SetCellValue(itemdung.DonVi);
                        if (itemdung.CoDinh == false || itemdung.CoDinh == null)
                        {
                            headerRow.CreateCell(3).SetCellValue(hamlamtron(itemdung.SLCapChoKhachVip * double.Parse(item.slkhachvip)));
                            headerRow.CreateCell(4).SetCellValue(hamlamtron(itemdung.SLCapChoKhachThuong * double.Parse(item.slkhachthuong)));
                            headerRow.CreateCell(5).SetCellValue(hamlamtron(itemdung.SLCapChoPhiCong * slphicong));
                            headerRow.CreateCell(6).SetCellValue(hamlamtron(itemdung.SLCapChoTiepVien * sltiepvien));
                        }
                        else
                        {
                            headerRow.CreateCell(3).SetCellValue(itemdung.SLCapChoKhachVip);
                            headerRow.CreateCell(4).SetCellValue(itemdung.SLCapChoKhachThuong);
                            headerRow.CreateCell(5).SetCellValue(itemdung.SLCapChoPhiCong);
                            headerRow.CreateCell(6).SetCellValue(itemdung.SLCapChoTiepVien);
                        }
                        DungCuExcel itemdungcungexcel = new DungCuExcel();
                        itemdungcungexcel.ID = IDalldungcu;
                        itemdungcungexcel.Madungcu = itemdung.MaDungCu;
                        itemdungcungexcel.Tendungcu = itemdung.TenDungCu;
                        itemdungcungexcel.DonVi = itemdung.DonVi;
                        itemdungcungexcel.slcapchokhachvip = itemdung.SLCapChoKhachVip;
                        itemdungcungexcel.slcapchokhachthuong = itemdung.SLCapChoKhachThuong;
                        itemdungcungexcel.slcapchophicong = itemdung.SLCapChoPhiCong;
                        itemdungcungexcel.slcapchotiepvien = itemdung.SLCapChoTiepVien;
                        itemdungcungexcel.slkhachviptrenchuyenbay = int.Parse(item.slkhachvip);
                        itemdungcungexcel.slkhachthuongtrenchuyenbay = int.Parse(item.slkhachthuong);
                        itemdungcungexcel.slphicongtrenchuyenbay = slphicong;
                        itemdungcungexcel.sltiepvientrenchuyenbay = sltiepvien;
                        itemdungcungexcel.codinh = itemdung.CoDinh ?? false;
                        itemdungcungexcel.MaMB = item.MaMB;
                        itemdungcungexcel.MaChuyenBay = item.MaBAY;
                        lstalldungcu.Add(itemdungcungexcel);
                    }
                }
                else
                {
                    List<ChiTietDungCuPhucVuChuyenBay> lsttmp = new List<ChiTietDungCuPhucVuChuyenBay>();
                    getslphicongvatiepvien(item.MaMB, item.MaBAY);
                    getchitietdungcuExcel(item.MaMB, item.MaBAY);
                    if (item.MaBAY == "CXRHAN")
                    {
                        if (long.Parse(item.slkhachvip) < 27)
                        {
                            if (long.Parse(cmbcatalogngoaikhay.SelectedValue.ToString()) == 3)
                            {
                                lsttmp = lstchitietdungcuExcel.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(6)).ToList();
                            }
                            else
                            {
                                lsttmp = lstchitietdungcuExcel.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(20)).ToList();
                            }
                        }
                        else
                        {
                            if (long.Parse(cmbcatalogngoaikhay.SelectedValue.ToString()) == 3)
                            {
                                lsttmp = lstchitietdungcuExcel.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(7)).ToList();
                            }
                            else
                            {
                                lsttmp = lstchitietdungcuExcel.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(21)).ToList();
                            }
                        }
                    }
                    if (item.MaBAY == "CXRSGN")
                    {
                        if (long.Parse(item.slkhachvip) < 27)
                        {
                            if (long.Parse(cmbcatalogngoaikhay.SelectedValue.ToString()) == 3)
                            {
                                lsttmp = lstchitietdungcuExcel.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(8)).ToList();
                            }
                            else
                            {
                                lsttmp = lstchitietdungcuExcel.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(22)).ToList();
                            }
                        }
                        else
                        {
                            if (long.Parse(cmbcatalogngoaikhay.SelectedValue.ToString()) == 3)
                            {
                                lsttmp = lstchitietdungcuExcel.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(9)).ToList();
                            }
                            else
                            {
                                lsttmp = lstchitietdungcuExcel.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(23)).ToList();
                            }
                        }
                    }
                    IRow headerRow = s1.CreateRow(row);
                    headerRow.CreateCell(0).SetCellValue("Mã Máy Bay");
                    headerRow.CreateCell(1).SetCellValue(item.MaMB);
                    headerRow.CreateCell(2).SetCellValue("Giờ bay:");
                    headerRow.CreateCell(3).SetCellValue(item.giobay);
                    row = row + 1;
                    headerRow = s1.CreateRow(row);
                    headerRow.CreateCell(0).SetCellValue("Mã chuyến bay");
                    headerRow.CreateCell(1).SetCellValue(item.MaBAY);
                    headerRow.CreateCell(2).SetCellValue("Ngày bay");
                    headerRow.CreateCell(3).SetCellValue(item.ngaybay);
                    row = row + 1;
                    headerRow = s1.CreateRow(row);
                    headerRow.CreateCell(0).SetCellValue("Loại phiếu");
                    headerRow.CreateCell(1).SetCellValue(loaiphieu);
                    row = row + 1;
                    headerRow = s1.CreateRow(row);
                    headerRow.CreateCell(0).SetCellValue("Mã dụng cụ");
                    headerRow.CreateCell(1).SetCellValue("Tên dụng cụ");
                    headerRow.CreateCell(2).SetCellValue("Đơn vị");
                    headerRow.CreateCell(3).SetCellValue("SL khách VIP: " + item.slkhachvip.ToString());
                    headerRow.CreateCell(4).SetCellValue("SL khách Thường: " + item.slkhachthuong.ToString());
                    headerRow.CreateCell(5).SetCellValue("Số phi công: " + slphicong);
                    headerRow.CreateCell(6).SetCellValue("Số tiếp viên: " + sltiepvien);
                    foreach (var itemdung in lsttmp)
                    {
                        row = row + 1;
                        IDalldungcu = IDalldungcu + 1;
                        headerRow = s1.CreateRow(row);
                        headerRow.CreateCell(0).SetCellValue(itemdung.MaDungCu);
                        headerRow.CreateCell(1).SetCellValue(itemdung.TenDungCu);
                        headerRow.CreateCell(2).SetCellValue(itemdung.DonVi);
                        if (itemdung.CoDinh == false || itemdung.CoDinh == null)
                        {
                            headerRow.CreateCell(3).SetCellValue(hamlamtron(itemdung.SLCapChoKhachVip * double.Parse(item.slkhachvip)));
                            headerRow.CreateCell(4).SetCellValue(hamlamtron(itemdung.SLCapChoKhachThuong * double.Parse(item.slkhachthuong)));
                            headerRow.CreateCell(5).SetCellValue(hamlamtron(itemdung.SLCapChoPhiCong * slphicong));
                            headerRow.CreateCell(6).SetCellValue(hamlamtron(itemdung.SLCapChoTiepVien * sltiepvien));
                        }
                        else
                        {
                            headerRow.CreateCell(3).SetCellValue(itemdung.SLCapChoKhachVip);
                            headerRow.CreateCell(4).SetCellValue(itemdung.SLCapChoKhachThuong);
                            headerRow.CreateCell(5).SetCellValue(itemdung.SLCapChoPhiCong);
                            headerRow.CreateCell(6).SetCellValue(itemdung.SLCapChoTiepVien);
                        }
                        DungCuExcel itemdungcungexcel = new DungCuExcel();
                        itemdungcungexcel.ID = IDalldungcu;
                        itemdungcungexcel.Madungcu = itemdung.MaDungCu;
                        itemdungcungexcel.Tendungcu = itemdung.TenDungCu;
                        itemdungcungexcel.DonVi = itemdung.DonVi;
                        itemdungcungexcel.slcapchokhachvip = itemdung.SLCapChoKhachVip;
                        itemdungcungexcel.slcapchokhachthuong = itemdung.SLCapChoKhachThuong;
                        itemdungcungexcel.slcapchophicong = itemdung.SLCapChoPhiCong;
                        itemdungcungexcel.slcapchotiepvien = itemdung.SLCapChoTiepVien;
                        itemdungcungexcel.slkhachviptrenchuyenbay = int.Parse(item.slkhachvip);
                        itemdungcungexcel.slkhachthuongtrenchuyenbay = int.Parse(item.slkhachthuong);
                        itemdungcungexcel.slphicongtrenchuyenbay = slphicong;
                        itemdungcungexcel.sltiepvientrenchuyenbay = sltiepvien;
                        itemdungcungexcel.codinh = itemdung.CoDinh ?? false;
                        itemdungcungexcel.MaMB = item.MaMB;
                        itemdungcungexcel.MaChuyenBay = item.MaBAY;
                        lstalldungcu.Add(itemdungcungexcel);
                    }
                }
                row = row + 2;
            }
            #endregion

            List<DungCuExcel> lstalldungcudistance = new List<DungCuExcel>();
            lstalldungcudistance = lstalldungcu.OrderBy(x => x.Madungcu).ToList();
            Int32 index = 0;
            while (index < lstalldungcudistance.Count - 1)
            {
                if (lstalldungcudistance[index].Madungcu == lstalldungcudistance[index + 1].Madungcu)
                    lstalldungcudistance.RemoveAt(index);
                else
                    index++;
            }
            int row2 = 1;

            double tongslcapchokhachvip = 0;
            double tongslcapchokhachthuong = 0;
            double tongslcapchophicong = 0;
            double tongslcapchotiepvien = 0;
            ISheet s2 = hssfworkbook.GetSheet("tongdungcu");
            s2.CreateRow(0).CreateCell(1).SetCellValue("Mã dụng cụ");
            s2.GetRow(0).CreateCell(2).SetCellValue("Tên dụng cụ");
            s2.GetRow(0).CreateCell(3).SetCellValue("Đơn vị");
            s2.GetRow(0).CreateCell(4).SetCellValue("Tổng SL Cấp Cho Khách VIP");
            s2.GetRow(0).CreateCell(5).SetCellValue("Tổng SL Cấp Cho Khách thường");
            s2.GetRow(0).CreateCell(6).SetCellValue("Tổng SL Cấp Cho Khách Phi Công");
            s2.GetRow(0).CreateCell(7).SetCellValue("Tổng SL Cấp Cho Khách tiếp viên");

            foreach (var item in lstalldungcudistance)
            {
                //IRow headerRow = s2.CreateRow(row);
                foreach (var itemAlldungcu in lstalldungcu)
                {
                    if (item.Madungcu == itemAlldungcu.Madungcu)
                    {
                            if (itemAlldungcu.codinh == false)
                            {
                                tongslcapchokhachvip = tongslcapchokhachvip + (hamlamtron(itemAlldungcu.slcapchokhachvip * itemAlldungcu.slkhachviptrenchuyenbay));
                                tongslcapchokhachthuong = tongslcapchokhachthuong + (hamlamtron(itemAlldungcu.slcapchokhachthuong * itemAlldungcu.slkhachthuongtrenchuyenbay));
                                tongslcapchophicong = tongslcapchophicong + (hamlamtron(itemAlldungcu.slcapchophicong * itemAlldungcu.slphicongtrenchuyenbay));
                                tongslcapchotiepvien = tongslcapchotiepvien + (hamlamtron(itemAlldungcu.slcapchotiepvien * itemAlldungcu.sltiepvientrenchuyenbay));
                            }
                            else
                            {
                                tongslcapchokhachvip = tongslcapchokhachvip + itemAlldungcu.slcapchokhachvip;
                                tongslcapchokhachthuong = tongslcapchokhachthuong + itemAlldungcu.slcapchokhachthuong;
                                tongslcapchophicong = tongslcapchophicong + itemAlldungcu.slcapchophicong;
                                tongslcapchotiepvien = tongslcapchotiepvien + itemAlldungcu.slcapchotiepvien;
                            }
                    }
                }


                s2.CreateRow(row2).CreateCell(1).SetCellValue(item.Madungcu);
                s2.GetRow(row2).CreateCell(2).SetCellValue(item.Tendungcu);
                s2.GetRow(row2).CreateCell(3).SetCellValue(item.DonVi);
                s2.GetRow(row2).CreateCell(4).SetCellValue(tongslcapchokhachvip);
                s2.GetRow(row2).CreateCell(5).SetCellValue(tongslcapchokhachthuong);
                s2.GetRow(row2).CreateCell(6).SetCellValue(tongslcapchophicong);
                s2.GetRow(row2).CreateCell(7).SetCellValue(tongslcapchotiepvien);
                
                row2 += 1;
                tongslcapchokhachvip = 0;
                tongslcapchokhachthuong = 0;
                tongslcapchophicong = 0;
                tongslcapchotiepvien = 0;
            }
            WriteToFile();
            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            string filename = @"xls\CopyMauDungCuPhucVuChuyenBay" + countexcel + ".xls";
            string workbookPath = Path.GetFullPath(filename);
            Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,
                     0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
                        true, false, 0, true, false, false);
            excelApp.Visible = true;
        }

        private void xuatexclanuong()
        {
            InitializeWorkbook();
            long idcataglog;
            lstalldungcu = new List<DungCuExcel>();
            int row = 1;
            long IDalldungcu = 0;
            for (int i = 0; i < cmbcatalogngoaikhay.Items.Count; i++)
            {
                cmbcatalogngoaikhay.SelectedIndex = i;
                idcataglog =long.Parse(cmbcatalogngoaikhay.SelectedValue.ToString());
                foreach (var itemchuyenbay in lstchuyenbay)
                {
                    getslphicongvatiepvien(itemchuyenbay.MaMB, itemchuyenbay.MaBAY);
                    getchitietdungcuExcel(itemchuyenbay.MaMB, itemchuyenbay.MaBAY);
                    if (itemchuyenbay.MaMB != "B777")
                    {
                        foreach (var itemdungcu in lstchitietdungcuExcel)
                        {
                            IDalldungcu = IDalldungcu + 1;
                            DungCuExcel itemdungcungexcel = new DungCuExcel();
                            itemdungcungexcel.ID = IDalldungcu;
                            itemdungcungexcel.Madungcu = itemdungcu.MaDungCu;
                            itemdungcungexcel.Tendungcu = itemdungcu.TenDungCu;
                            itemdungcungexcel.DonVi = itemdungcu.DonVi;
                            itemdungcungexcel.slcapchokhachvip = itemdungcu.SLCapChoKhachVip;
                            itemdungcungexcel.slcapchokhachthuong = itemdungcu.SLCapChoKhachThuong;
                            itemdungcungexcel.slcapchophicong = itemdungcu.SLCapChoPhiCong;
                            itemdungcungexcel.slcapchotiepvien = itemdungcu.SLCapChoTiepVien;
                            itemdungcungexcel.slkhachviptrenchuyenbay = int.Parse(itemchuyenbay.slkhachvip);
                            itemdungcungexcel.slkhachthuongtrenchuyenbay = int.Parse(itemchuyenbay.slkhachthuong);
                            itemdungcungexcel.slphicongtrenchuyenbay = slphicong;
                            itemdungcungexcel.sltiepvientrenchuyenbay = sltiepvien;
                            itemdungcungexcel.codinh = itemdungcu.CoDinh ?? false;
                            itemdungcungexcel.MaMB = itemchuyenbay.MaMB;
                            itemdungcungexcel.MaChuyenBay = itemchuyenbay.MaBAY;
                            lstalldungcu.Add(itemdungcungexcel);
                        }
                    }
                    else
                    {
                        List<ChiTietDungCuPhucVuChuyenBay> lsttmp = new List<ChiTietDungCuPhucVuChuyenBay>();
                        #region Xu ly chuyen bay B777
                        if (itemchuyenbay.MaBAY == "CXRHAN")
                        {
                            if (long.Parse(itemchuyenbay.slkhachvip) < 27)
                            {
                                if (long.Parse(cmbcatalogngoaikhay.SelectedValue.ToString()) == 3)
                                {
                                    lsttmp = lstchitietdungcuExcel.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(6)).ToList();
                                }
                                else
                                {
                                    lsttmp = lstchitietdungcuExcel.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(20)).ToList();
                                }
                            }
                            else
                            {
                                if (long.Parse(cmbcatalogngoaikhay.SelectedValue.ToString()) == 3)
                                {
                                    lsttmp = lstchitietdungcuExcel.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(7)).ToList();
                                }
                                else
                                {
                                    lsttmp = lstchitietdungcuExcel.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(21)).ToList();
                                }
                            }
                        }
                        if (itemchuyenbay.MaBAY == "CXRSGN")
                        {
                            if (long.Parse(itemchuyenbay.slkhachvip) < 27)
                            {
                                if (long.Parse(cmbcatalogngoaikhay.SelectedValue.ToString()) == 3)
                                {
                                    lsttmp = lstchitietdungcuExcel.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(8)).ToList();
                                }
                                else
                                {
                                    lsttmp = lstchitietdungcuExcel.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(22)).ToList();
                                }
                            }
                            else
                            {
                                if (long.Parse(cmbcatalogngoaikhay.SelectedValue.ToString()) == 3)
                                {
                                    lsttmp = lstchitietdungcuExcel.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(9)).ToList();
                                }
                                else
                                {
                                    lsttmp = lstchitietdungcuExcel.Where(x => x.IDDungCuPhucVuChuyenBay.Equals(23)).ToList();
                                }
                            }
                        }
                        #endregion

                        foreach (var itemdungcu in lsttmp)
                        {
                            DungCuExcel itemdungcungexcel = new DungCuExcel();
                            itemdungcungexcel.ID = IDalldungcu;
                            itemdungcungexcel.Madungcu = itemdungcu.MaDungCu;
                            itemdungcungexcel.Tendungcu = itemdungcu.TenDungCu;
                            itemdungcungexcel.DonVi = itemdungcu.DonVi;
                            itemdungcungexcel.slcapchokhachvip = itemdungcu.SLCapChoKhachVip;
                            itemdungcungexcel.slcapchokhachthuong = itemdungcu.SLCapChoKhachThuong;
                            itemdungcungexcel.slcapchophicong = itemdungcu.SLCapChoPhiCong;
                            itemdungcungexcel.slcapchotiepvien = itemdungcu.SLCapChoTiepVien;
                            itemdungcungexcel.slkhachviptrenchuyenbay = int.Parse(itemchuyenbay.slkhachvip);
                            itemdungcungexcel.slkhachthuongtrenchuyenbay = int.Parse(itemchuyenbay.slkhachthuong);
                            itemdungcungexcel.slphicongtrenchuyenbay = slphicong;
                            itemdungcungexcel.sltiepvientrenchuyenbay = sltiepvien;
                            itemdungcungexcel.codinh = itemdungcu.CoDinh ?? false;
                            itemdungcungexcel.MaMB = itemchuyenbay.MaMB;
                            itemdungcungexcel.MaChuyenBay = itemchuyenbay.MaBAY;
                            lstalldungcu.Add(itemdungcungexcel);
                        }
                    }
                }
            }

            List<DungCuExcel> lstalldungcudistance = new List<DungCuExcel>();
            lstalldungcudistance = lstalldungcu.OrderBy(x => x.Madungcu).ToList();
            Int32 index = 0;
            while (index < lstalldungcudistance.Count - 1)
            {
                if (lstalldungcudistance[index].Madungcu == lstalldungcudistance[index + 1].Madungcu)
                    lstalldungcudistance.RemoveAt(index);
                else
                    index++;
            }
            double tongslcapchokhachvip = 0;
            double tongslcapchokhachthuong = 0;
            double tongslcapchophicong = 0;
            double tongslcapchotiepvien = 0;
            ISheet s2 = hssfworkbook.GetSheet("tongdungcu");
            s2.CreateRow(0).CreateCell(1).SetCellValue("Mã dụng cụ");
            s2.GetRow(0).CreateCell(2).SetCellValue("Tên dụng cụ");
            s2.GetRow(0).CreateCell(3).SetCellValue("Đơn vị");
            s2.GetRow(0).CreateCell(4).SetCellValue("Tổng SL Cấp Cho Khách VIP");
            s2.GetRow(0).CreateCell(5).SetCellValue("Tổng SL Cấp Cho Khách thường");
            s2.GetRow(0).CreateCell(6).SetCellValue("Tổng SL Cấp Cho Khách Phi Công");
            s2.GetRow(0).CreateCell(7).SetCellValue("Tổng SL Cấp Cho Khách tiếp viên");
            foreach (var item in lstalldungcudistance)
            {
                foreach (var itemAlldungcu in lstalldungcu)
                {
                    if (item.Madungcu == itemAlldungcu.Madungcu)
                    {
                        if (itemAlldungcu.codinh == false)
                        {
                            tongslcapchokhachvip = tongslcapchokhachvip + (hamlamtron(itemAlldungcu.slcapchokhachvip * itemAlldungcu.slkhachviptrenchuyenbay));
                            tongslcapchokhachthuong = tongslcapchokhachthuong + (hamlamtron(itemAlldungcu.slcapchokhachthuong * itemAlldungcu.slkhachthuongtrenchuyenbay));
                            tongslcapchophicong = tongslcapchophicong + (hamlamtron(itemAlldungcu.slcapchophicong * itemAlldungcu.slphicongtrenchuyenbay));
                            tongslcapchotiepvien = tongslcapchotiepvien + (hamlamtron(itemAlldungcu.slcapchotiepvien * itemAlldungcu.sltiepvientrenchuyenbay));
                        }
                        else
                        {
                            tongslcapchokhachvip = tongslcapchokhachvip + itemAlldungcu.slcapchokhachvip;
                            tongslcapchokhachthuong = tongslcapchokhachthuong + itemAlldungcu.slcapchokhachthuong;
                            tongslcapchophicong = tongslcapchophicong + itemAlldungcu.slcapchophicong;
                            tongslcapchotiepvien = tongslcapchotiepvien + itemAlldungcu.slcapchotiepvien;
                        }
                    }
                }
                row += 1;
                s2.CreateRow(row).CreateCell(1).SetCellValue(item.Madungcu);
                s2.GetRow(row).CreateCell(2).SetCellValue(item.Tendungcu);
                s2.GetRow(row).CreateCell(3).SetCellValue(item.DonVi);
                s2.GetRow(row).CreateCell(4).SetCellValue(tongslcapchokhachvip);
                s2.GetRow(row).CreateCell(5).SetCellValue(tongslcapchokhachthuong);
                s2.GetRow(row).CreateCell(6).SetCellValue(tongslcapchophicong);
                s2.GetRow(row).CreateCell(7).SetCellValue(tongslcapchotiepvien);
                tongslcapchokhachvip = 0;
                tongslcapchokhachthuong = 0;
                tongslcapchophicong = 0;
                tongslcapchotiepvien = 0;
            }

            WriteToFileAnUong();
            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            string filename = @"xls\CopyMauDungCuPhucVuChuyenBayAnVaUong" + countexcel + ".xls";
            string workbookPath = Path.GetFullPath(filename);
            Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,
                     0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
                        true, false, 0, true, false, false);
            excelApp.Visible = true;
        }

        private double hamlamtron(double socanlamtron)
        {
            return Math.Round(socanlamtron, 0);
        }

        private void getslphicongvatiepvien(string mamb,string machuyenbay)
        {
            IList<ChuyenBay> lsttmp;
            lsttmp = db.getslphicongtiepvienByMaMBAndMaChuyenBay(mamb, machuyenbay).ExecuteTypedList<ChuyenBay>();
            var item = lsttmp.FirstOrDefault();
            if (item != null)
            {
                slphicong = item.SoPhiCong ?? 0;
                sltiepvien = item.SoTiepVien ?? 0;
            }
        }

        public FrmTinhNguyenVatLieuChuyenBay()
        {
            InitializeComponent();
            frmtinhnguyenvatlieu = this;
            InitCatalog();
        }

        private void btndocdulieu_Click(object sender, EventArgs e)
        {
            readfileExcelimporttoList();
            
            //autoinsertdatabaseformexcel();
        }

        private void FrmTinhNguyenVatLieuChuyenBay_FormClosed(object sender, FormClosedEventArgs e)
        {
            frmtinhnguyenvatlieu = null;
        }

        private void dataGridView1_CellEnter(object sender, DataGridViewCellEventArgs e)
        {
            InitchiTietdungcu();
            IList<ChuyenBay> lsttmp;
            lsttmp = db.getslphicongtiepvienByMaMBAndMaChuyenBay(gvchuyenbay.CurrentRow.Cells["maMBDataGridViewTextBoxColumn"].Value.ToString(), gvchuyenbay.CurrentRow.Cells["maBAYDataGridViewTextBoxColumn"].Value.ToString()).ExecuteTypedList<ChuyenBay>();
            var item = lsttmp.FirstOrDefault();
            if (item != null)
            {
                txtslphicong.Text = item.SoPhiCong.ToString();
                txtsltiepvien.Text = item.SoTiepVien.ToString();
            }
        }

        private void btnxuatexcel_Click(object sender, EventArgs e)
        {
            xuatexcelchitiet();
            countexcel = countexcel + 1;
        }

        private void cmbcatalogngoaikhay_SelectedValueChanged(object sender, EventArgs e)
        {
            InitchiTietdungcu();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            xuatexclanuong();
            countexcel = countexcel + 1;
        }
    }
}
